﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;

namespace OgilvyOne.Utility
{
    public class SqlTool
    {
        private static string name = null;
        private static StringBuilder sql = null;
        private static SqlParameter[] parameter = null;
        public static string buildUpdateSql(object obj)
        {
            if (obj != null)
            {
                Type type = obj.GetType();
                PropertyInfo[] piArray = type.GetProperties();
                object value = null;
                sql = new StringBuilder(string.Format("update {0} set ", type.Name));
                foreach (PropertyInfo pi in piArray)
                {
                    value = pi.GetValue(obj,null);
                    switch (pi.PropertyType.Name)
                    {
                        case "Int32":
                            if (!pi.Name.Equals(piArray[0].Name))
                            {
                                sql.Append(OgilvyOne.Utility.SqlTool.addSql(Convert.ToInt32(value), lowerFirst(pi.Name)));
                            }
                            break;
                        case "String":
                            sql.Append(OgilvyOne.Utility.SqlTool.addSql(value==null?"":value.ToString(), lowerFirst(pi.Name)));
                            break;
                    }
                }
                sql.Remove(sql.ToString().LastIndexOf(','), 1).Append(string.Format(" where {0}={1}", piArray[0].Name, Convert.ToInt32(piArray[0].GetValue(obj,null))));
            }
            return sql.ToString();
        }
        public static string buildInsertSql(object obj)
        {
            if (obj != null)
            {
                Type type = obj.GetType();
                PropertyInfo[] piArray = type.GetProperties();
                object value = null;
                sql = new StringBuilder(string.Format("insert {0} ( ", type.Name));
                StringBuilder valuesql = new StringBuilder(")values(");
                foreach (PropertyInfo pi in piArray)
                {
                    value = pi.GetValue(obj, null);
                    switch (pi.PropertyType.Name)
                    {
                        case "Int32":
                            if ((int)value > 0)
                            {
                                sql.Append(pi.Name);
                                valuesql.Append(value);
                                sql.Append(",");
                                valuesql.Append(",");
                            }
                            break;
                        case "String":
                            if (value != null)
                            {
                                sql.Append(pi.Name);
                                valuesql.Append(string.Format("'{0}'", value));
                                sql.Append(",");
                                valuesql.Append(",");
                            }
                            break;
                    }
                }
                sql.Remove(sql.ToString().LastIndexOf(','), 1).Append(valuesql.Remove(valuesql.ToString().LastIndexOf(','), 1).Append(");SELECT @@IDENTITY"));
            }
            return sql.ToString();
        }
        private static string lowerFirst(string str)
        {
            return str.Substring(0, 1).ToLower() + str.Substring(1);
        }
        public static object[] buildSql(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] piArray = type.GetProperties();
            int id = (int)piArray[0].GetValue(obj, null);
            int length = piArray.Length;
            StringBuilder paramStr = new StringBuilder();
                for (int i = 1; i < length; i++)
                {
                    object value = piArray[i].GetValue(obj, null);
                    name = piArray[i].Name;
                    switch (piArray[i].PropertyType.Name)
                    {
                        case "Int32":
                            if ((int)value > 0)
                            {
                                paramStr.Append(name + ",");
                            }
                            break;
                        case "Double":
                            if ((double)value >= 0)
                            {
                                paramStr.Append(name + ",");
                            }
                            break;
                        case "String":
                            if (value != null)
                            {
                                paramStr.Append(name + ",");
                            }
                            break;
                    }
                }
            string str = paramStr.ToString().Substring(0, paramStr.ToString().LastIndexOf(","));
            string[] strArray = str.Split(',');
            length = strArray.Length;
            if (id == 0)
            {
                sql = new StringBuilder(string.Format("insert {0}(", type.Name));
                sql.Append(string.Format("{0})values(", str));
                parameter = new SqlParameter[length];
                appendParam(id, length, strArray, type, obj);
                sql.Append("); SELECT @@IDENTITY");
            }
            else
            {
                sql = new StringBuilder(string.Format("update {0} set ", type.Name));
                parameter = new SqlParameter[length + 1];
                appendParam(id, length, strArray, type, obj);
                sql.Append(string.Format(" where {0}=@{0}", piArray[0].Name));
                parameter[length] = new SqlParameter(string.Format("@{0}", piArray[0].Name), id);
            }
            object[] objs = new object[] { sql, parameter, piArray[0].GetValue(obj, null) };
            return objs;
        }
        private static void appendParam(int id,int length,string[] strArray,Type type,object obj)
        {
            PropertyInfo pi = null;
            for (int i = 0; i < length; i++)
            {
                name = strArray[i];
                if (id == 0)
                {
                    sql.Append(string.Format("@{0}", name));
                }
                else
                {
                    sql.Append(string.Format("{0}=@{0}",name));
                }
                if (i + 1 != length)
                {
                    sql.Append(",");
                }
                pi = type.GetProperty(strArray[i]);
                object value = pi.GetValue(obj, null);
                parameter[i] = new SqlParameter(string.Format("@{0}", name), value);
            }
        }
        public static string addSql(string value, string field)
        {
            if (string.IsNullOrEmpty(value))
            {
                return string.Format("{0}=null,", field);
            }
            else
            {
                return string.Format("{0}='{1}',", field, value);
            }
        }
        public static string addSql(int value, string field)
        {
            if (value == 0)
            {
                return string.Format("{0}=null,", field);
            }
            else
            {
                return string.Format("{0}={1},", field, value);
            }
        }
    }
}
